package com.yue.ssa.common;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ExcelUtils {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    public static void download(HttpServletResponse response, List<Map<String, Object>> dataList, List<String> header, Map<Integer, String> replaceData, String fileName, int start) {
        logger.info("正在导出文件");
        XSSFWorkbook wb = null;
        Resource resource = new ClassPathResource(fileName);
        try {
            InputStream inputStream = new FileInputStream(resource.getFile());
            wb = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = wb.getSheetAt(0);
            if (replaceData != null && replaceData.size() > 0) {
                replaceWord(replaceData, sheet, start);
            }
            if (dataList.size() != 0) {
                setCellValue(wb, sheet, dataList, header, start);
            }
            toExcel(fileName, response, wb);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void replaceWord(Map<Integer, String> data, XSSFSheet sheet, int end) {
        for (int i = 0; i < end; i++) {
            XSSFRow row = sheet.getRow(i);
            for (int j = 0; j < row.getLastCellNum(); j++) {
                XSSFCell cell = row.getCell(j);
                String value = cell.getStringCellValue();
//                System.out.println(i + "行" + j + "列:" + value);
                if (value.contains("{{")) {
                    String temp = value.substring(0, value.lastIndexOf("}") + 1);
                    String a = value.substring(2, value.indexOf("}"));
                    int b = Integer.parseInt(a);
                    cell.setCellValue(value.replace(temp, data.get(b)));
                }
            }
        }
    }

    private static void setCellValue(XSSFWorkbook workbook, Sheet sheet, List<Map<String, Object>> tableInfoList, List<String> enList, int start) {
        for (int i = 0; i < tableInfoList.size(); i++) {
            //表头是第0行，所以从第一行开始创建
            Row row = sheet.createRow(i + start);
            for (int j = 0; j < enList.size(); j++) {
                Cell cell = row.createCell(j);
                setCellStyle(workbook, cell, 10, false);
                Object obj = tableInfoList.get(i).get(enList.get(j));
                if (obj == null) {
                    cell.setCellValue(" ");
                } else {
                    if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                    } else {
                        cell.setCellValue(obj.toString());
                    }
                }
            }
            sheet.autoSizeColumn(i);
        }
    }

    private static void setCellStyle(XSSFWorkbook workbook, Cell cell, int fontSize, boolean isBold) {
        //创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        //左右居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //自动换行
        cellStyle.setWrapText(true);
        //添加字体样式
        Font font = workbook.createFont();
        //字号(大小)
        font.setFontHeightInPoints((short) fontSize);
        if (isBold) {
            //加粗
            font.setBold(isBold);
        }
        //字体样式生效
        cellStyle.setFont(font);
        //样式生效
        cell.setCellStyle(cellStyle);
    }

    private static void toExcel(String filename, HttpServletResponse response, XSSFWorkbook workbook) {
        try {
            filename = new String(filename.getBytes("gbk"), "iso8859-1");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", String.format("attachment; filename=" + filename));
            response.flushBuffer();
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (Exception e) {
            logger.error("数据导出失败", e);
            e.printStackTrace();
        }
    }

    public static List<Row> getDataFromExcel(MultipartFile multipartFile) throws Exception {
        List<Row> rowList = new ArrayList<>();
        String fileName = multipartFile.getOriginalFilename();
        //判断是否为excel类型文件
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            logger.error("文件不是excel类型，数据导入失败");
            return rowList;
        }
        logger.info("正在读取数据...");
        InputStream inputStream = multipartFile.getInputStream();
        Workbook workbook = null;
        //2003版本的excel，用.xls结尾
        if (fileName.endsWith(".xls")) {
            workbook = new HSSFWorkbook(inputStream);//得到工作簿
        } else if (fileName.endsWith(".xlsx")) {
            //2007版本的excel，用.xlsx结尾
            workbook = new XSSFWorkbook(OPCPackage.open(inputStream));//OPCPackage.open(is)取得一个文件的读写权限
        }
        Sheet sheet = workbook.getSheetAt(0);
        //获得数据的总行数
        int totalRowNum = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < totalRowNum; i++) {
            rowList.add(sheet.getRow(i));
        }
        return rowList;
    }
}
